import org.json.JSONObject;

import java.net.URLEncoder;
import java.sql.*;

/**
 * Created by Kroc on 2016/1/04.
 */
public class Database {
    private String url="jdbc:mysql://localhost:3306/homework_5?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT ";
    private Connection conn;
    public void link()throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        conn= DriverManager.getConnection(url,"root",null);
        System.out.println("Connection built:");
    }

    public void Capture(String Url)throws Exception
    {
        link();
        int num=0;
        String sql="";
        try{
            for(int tid=15;tid<=166;tid++)
            {
                int page=1 ;
                int code;
                while (true)
                {
                    JSONObject jsonObject=new GetHttp().getjson(Url+"tid"+"="+tid+"&pn="+page);
                    page++;
                    code=(Integer) jsonObject.get("code");
                    if(code==-40002)break;
                    JSONObject data=jsonObject.getJSONObject("data");
                    JSONObject archives=data.getJSONObject("archives");
                    for(int i=0;i<archives.length();i++)
                    {
                        JSONObject temp=(JSONObject) archives.get(""+i+"");
                        String author = URLEncoder.encode("" + temp.getString("author") + "", "utf-8");
                        JSONObject stat=temp.getJSONObject("stat");
                        sql = "insert ignore into data(av,author,categories,coin,favorites)values(" + "'" + temp.get("aid") + "','" + author + "','"
                                + temp.get("tid") + "','" + stat.get("coin") + "','" + temp.get("favorites") + "')";
                        PreparedStatement pst = conn.prepareStatement(sql);
                        pst.executeUpdate();
                        System.out.println(++num+"  "+page);
                    }
                }
            }
        }finally {
            conn.close();
        }
    }
    public void DownloadAll(int x)throws Exception{
        Download dl=new Download();
        String u="http://www.ibilibili.com/video/av";
        link();
        Statement statement=conn.createStatement();
        String sql="select * from data order by favorites desc limit "+x+"";
        ResultSet resultSet=statement.executeQuery(sql);
        while(resultSet.next())
        {
            String url=u+resultSet.getString("av");
            dl.download(dl.getURL(url),"av"+resultSet.getString("av")+".mp4","D:\\homework_5\\src\\main\\resources");
            System.out.println("av"+resultSet.getString("av")+";"+"Download finished");
        }
    }
}
